﻿using log4net;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace NCP.Util
{
    public static class ExcelHelper
    {
        private static ILog logger = LogManager.GetLogger(typeof(ExcelHelper));
        public static DataSet ExcelToDataSet(string excelPath)
        {
            return ExcelToDataSet(excelPath, true);
        }

        public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader)
        {
            int sheetCount;
            return ExcelToDataSet(excelPath, firstRowAsHeader, out sheetCount);
        }

        public static DataSet ExcelToDataSet(string excelPath, bool firstRowAsHeader, out int sheetCount)
        {
            using (DataSet ds = new DataSet())
            {
                using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(fileStream);

                    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);

                    sheetCount = workbook.NumberOfSheets;

                    for (int i = 0; i < sheetCount; ++i)
                    {
                        HSSFSheet sheet = workbook.GetSheetAt(i) as HSSFSheet;
                        DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
                        ds.Tables.Add(dt);
                    }

                    return ds;
                }
            }
        }

        public static DataTable ExcelToDataTable(string excelPath, string sheetName = "")
        {
            return ExcelToDataTable(excelPath, sheetName, true);
        }

        public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader)
        {
            using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook workbook = new HSSFWorkbook(fileStream);

                HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);

                if (string.IsNullOrEmpty(sheetName))
                    sheetName = workbook.GetSheetName(0);

                HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet;

                return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
            }
        }

        private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, bool firstRowAsHeader)
        {
            if (firstRowAsHeader)
            {
                return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
            }
            else
            {
                return ExcelToDataTable(sheet, evaluator);
            }
        }

        private static DataTable ExcelToDataTableFirstRowAsHeader(HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                HSSFRow firstRow = sheet.GetRow(0) as HSSFRow;
                int cellCount = GetCellCount(sheet);

                for (int i = 0; i < cellCount; i++)
                {
                    if (firstRow.GetCell(i) != null)
                    {
                        dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
                    }
                    else
                    {
                        dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
                    }
                }

                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    HSSFRow row = sheet.GetRow(i) as HSSFRow;
                    DataRow dr = dt.NewRow();
                    FillDataRowByHSSFRow(row, evaluator, ref dr);
                    dt.Rows.Add(dr);
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }

        private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
        {
            using (DataTable dt = new DataTable())
            {
                if (sheet.LastRowNum != 0)
                {
                    int cellCount = GetCellCount(sheet);

                    for (int i = 0; i < cellCount; i++)
                    {
                        dt.Columns.Add(string.Format("F{0}", i), typeof(string));
                    }

                    for (int i = 0; i < sheet.FirstRowNum; ++i)
                    {
                        DataRow dr = dt.NewRow();
                        dt.Rows.Add(dr);
                    }

                    for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
                    {
                        HSSFRow row = sheet.GetRow(i) as HSSFRow;
                        DataRow dr = dt.NewRow();
                        FillDataRowByHSSFRow(row, evaluator, ref dr);
                        dt.Rows.Add(dr);
                    }
                }

                dt.TableName = sheet.SheetName;
                return dt;
            }
        }

        private static void FillDataRowByHSSFRow(HSSFRow row, HSSFFormulaEvaluator evaluator, ref DataRow dr)
        {
            if (row != null)
            {
                for (int j = 0; j < dr.Table.Columns.Count; j++)
                {
                    HSSFCell cell = row.GetCell(j) as HSSFCell;

                    if (cell != null)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.Blank:
                                dr[j] = DBNull.Value;
                                break;
                            case CellType.Boolean:
                                dr[j] = cell.BooleanCellValue;
                                break;
                            case CellType.Numeric:
                                if (DateUtil.IsCellDateFormatted(cell))
                                {
                                    dr[j] = cell.DateCellValue;
                                }
                                else
                                {
                                    dr[j] = cell.NumericCellValue;
                                }
                                break;
                            case CellType.String:
                                dr[j] = cell.StringCellValue;
                                break;
                            case CellType.Error:
                                dr[j] = cell.ErrorCellValue;
                                break;
                            case CellType.Formula:
                                cell = evaluator.EvaluateInCell(cell) as HSSFCell;
                                dr[j] = cell.ToString();
                                break;
                            default:
                                throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
                        }
                    }
                }
            }
        }

        private static int GetCellCount(HSSFSheet sheet)
        {
            int firstRowNum = sheet.FirstRowNum;

            int cellCount = 0;

            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
            {
                HSSFRow row = sheet.GetRow(i) as HSSFRow;

                if (row != null && row.LastCellNum > cellCount)
                {
                    cellCount = row.LastCellNum;
                }
            }

            return cellCount;
        }

        public static string ExportToExcel<T>(List<T> tList, string name)
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            string filePath = GetDateTimeRnd(name) + ".xls";
            string saveFileName = Path.Combine(path, filePath);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

            Dictionary<PropertyInfo, string> dictCol = new Dictionary<PropertyInfo, string>();
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                object[] attrs = pi.GetCustomAttributes(typeof(DescriptionAttribute), false);
                if (attrs.Length > 0)
                {
                    dictCol.Add(pi, (attrs[0] as DescriptionAttribute).Description);
                }
            }

            //标题样式
            HSSFCellStyle cellTitleStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellTitleStyle.BorderBottom = BorderStyle.Thin;
            cellTitleStyle.BorderLeft = BorderStyle.Thin;
            cellTitleStyle.BorderRight = BorderStyle.Thin;
            cellTitleStyle.BorderTop = BorderStyle.Thin;

            HSSFFont dataTitleFont = workbook.CreateFont() as HSSFFont;
            dataTitleFont.FontName = "宋体";
            dataTitleFont.IsBold = true;
            dataTitleFont.FontHeightInPoints = 12;
            cellTitleStyle.SetFont(dataTitleFont);
            //标题
            HSSFRow row = sheet.CreateRow(0) as HSSFRow;
            int rowIndex = 1; int colIndex = 1;
            HSSFCell cell1 = row.CreateCell(0) as HSSFCell;
            cell1.SetCellValue("编号");
            cell1.CellStyle = cellTitleStyle;
            foreach (string desc in dictCol.Values)
            {
                HSSFCell cell = row.CreateCell(colIndex) as HSSFCell;
                cell.SetCellValue(desc);
                cell.CellStyle = cellTitleStyle;
                colIndex++;
            }

            //数据样式
            HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            tList.ForEach(t =>
            {
                HSSFRow vRow = sheet.CreateRow(rowIndex) as HSSFRow;
                colIndex = 1;
                HSSFCell cell2 = vRow.CreateCell(0) as HSSFCell;
                cell2.SetCellValue(rowIndex);
                cell2.CellStyle = cellStyle;
                foreach (PropertyInfo pi in dictCol.Keys)
                {
                    HSSFCell cell = vRow.CreateCell(colIndex) as HSSFCell;
                    object val = pi.GetValue(t, null);
                    if (val != null)
                    {
                        cell.SetCellValue(val.ToString());

                    }
                    cell.CellStyle = cellStyle;
                    colIndex++;
                }
                rowIndex++;
            });

            //调整列宽
            colIndex = 0;
            foreach (string desc in dictCol.Values)
            {
                sheet.AutoSizeColumn(colIndex, true);
            }

            using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            return saveFileName;
        }

        public static string ExportToExcel(DataTable dt, string name, string title = "", bool showIndex = false, bool Landscape = false)
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            string filePath = GetDateTimeRnd(name) + ".xls";
            string saveFileName = Path.Combine(path, filePath);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

            //标题样式
            HSSFCellStyle cellTitleStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellTitleStyle.BorderBottom = BorderStyle.Thin;
            cellTitleStyle.BorderLeft = BorderStyle.Thin;
            cellTitleStyle.BorderRight = BorderStyle.Thin;
            cellTitleStyle.BorderTop = BorderStyle.Thin;
            cellTitleStyle.Alignment = HorizontalAlignment.Center;

            HSSFFont dataTitleFont = workbook.CreateFont() as HSSFFont;
            dataTitleFont.FontName = "宋体";
            dataTitleFont.IsBold = true;
            //dataTitleFont.FontHeightInPoints = 12;
            cellTitleStyle.SetFont(dataTitleFont);
            int i = 0;
            if (!string.IsNullOrEmpty(title))
            {
                HSSFRow rowTitle = sheet.CreateRow(0) as HSSFRow;
                HSSFCell cell = rowTitle.CreateCell(0) as HSSFCell;
                cell.SetCellValue(title);
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, showIndex ? dt.Columns.Count : dt.Columns.Count - 1));
                cell.CellStyle = cellTitleStyle;
                i++;
            }
            //标题
            HSSFRow row = sheet.CreateRow(0 + i) as HSSFRow;
            int rowIndex = 1 + i;
            int rowShowIndex = 1;
            int colIndex = showIndex ? 1 : 0;
            if (showIndex)
            {
                HSSFCell cell1 = row.CreateCell(0) as HSSFCell;
                cell1.SetCellValue("编号");
                cell1.CellStyle = cellTitleStyle;
            }
            foreach (DataColumn dc in dt.Columns)
            {
                HSSFCell cell = row.CreateCell(colIndex) as HSSFCell;
                cell.SetCellValue(dc.ColumnName);
                cell.CellStyle = cellTitleStyle;
                colIndex++;
            }

            //数据样式
            HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            foreach (DataRow dr in dt.Rows)
            {
                HSSFRow vRow = sheet.CreateRow(rowIndex) as HSSFRow;
                colIndex = showIndex ? 1 : 0;
                if (showIndex)
                {
                    HSSFCell cell2 = vRow.CreateCell(0) as HSSFCell;
                    cell2.SetCellValue(rowShowIndex);
                    cell2.CellStyle = cellStyle;
                    rowShowIndex++;
                }
                foreach (DataColumn dc in dt.Columns)
                {
                    HSSFCell cell = vRow.CreateCell(colIndex) as HSSFCell;
                    if (dr[dc] != DBNull.Value)
                    {
                        object value = dr[dc];
                        if (value is decimal || value is double || value is float || value is int)
                            cell.SetCellValue(Convert.ToDouble(value));
                        else
                            cell.SetCellValue(value.ToString());
                    }
                    cell.CellStyle = cellStyle;
                    colIndex++;
                }
                rowIndex++;
            }

            //调整列宽 
            int jCount = showIndex ? dt.Columns.Count + 1 : dt.Columns.Count;
            for (int j = 0; j < jCount; j++)
            {
                sheet.AutoSizeColumn(j, true);
            }
            sheet.PrintSetup.Landscape = Landscape;//设置横向打印
            using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            return saveFileName;
        }

        public static string ExportToExcelByCaption(DataTable dt, string name, string sheetName, string saveFileName = "", string title = "", bool Landscape = false)
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (string.IsNullOrEmpty(saveFileName))
            {
                string filePath = GetDateTimeRnd(name) + ".xls";
                saveFileName = Path.Combine(path, filePath);
            }
            else
            {
                using (var file = new FileStream(saveFileName, FileMode.Open, FileAccess.Read))
                {
                    workbook = new HSSFWorkbook(file);
                }
            }
            HSSFSheet sheet = workbook.CreateSheet(sheetName) as HSSFSheet;

            //标题样式
            HSSFCellStyle cellTitleStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellTitleStyle.BorderBottom = BorderStyle.Thin;
            cellTitleStyle.BorderLeft = BorderStyle.Thin;
            cellTitleStyle.BorderRight = BorderStyle.Thin;
            cellTitleStyle.BorderTop = BorderStyle.Thin;
            cellTitleStyle.Alignment = HorizontalAlignment.Center;

            HSSFFont dataTitleFont = workbook.CreateFont() as HSSFFont;
            dataTitleFont.FontName = "宋体";
            dataTitleFont.IsBold = true;
            dataTitleFont.FontHeightInPoints = 12;
            cellTitleStyle.SetFont(dataTitleFont);
            int i = 0;
            if (!string.IsNullOrEmpty(title))
            {
                HSSFRow rowTitle = sheet.CreateRow(0) as HSSFRow;
                HSSFCell cell = rowTitle.CreateCell(0) as HSSFCell;
                cell.SetCellValue(title);
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
                cell.CellStyle = cellTitleStyle;
                i++;
            }
            //标题
            HSSFRow row = sheet.CreateRow(0 + i) as HSSFRow;
            int rowIndex = 1 + i; int colIndex = 1;
            HSSFCell cell1 = row.CreateCell(0) as HSSFCell;
            cell1.SetCellValue("编号");
            cell1.CellStyle = cellTitleStyle;
            foreach (DataColumn dc in dt.Columns)
            {
                if (string.IsNullOrEmpty(dc.Caption) || dc.Caption == dc.ColumnName)
                    continue;
                HSSFCell cell = row.CreateCell(colIndex) as HSSFCell;
                cell.SetCellValue(dc.Caption);
                cell.CellStyle = cellTitleStyle;
                colIndex++;
            }

            //数据样式
            HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            foreach (DataRow dr in dt.Rows)
            {
                HSSFRow vRow = sheet.CreateRow(rowIndex) as HSSFRow;
                colIndex = 1;
                HSSFCell cell2 = vRow.CreateCell(0) as HSSFCell;
                cell2.SetCellValue(rowIndex);
                cell2.CellStyle = cellStyle;
                foreach (DataColumn dc in dt.Columns)
                {
                    if (string.IsNullOrEmpty(dc.Caption) || dc.Caption == dc.ColumnName)
                        continue;
                    HSSFCell cell = vRow.CreateCell(colIndex) as HSSFCell;
                    if (dr[dc] != DBNull.Value)
                    {
                        cell.SetCellValue(dr[dc].ToString());

                    }
                    cell.CellStyle = cellStyle;
                    colIndex++;
                }
                rowIndex++;
            }

            //调整列宽
            colIndex = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                if (string.IsNullOrEmpty(dc.Caption) || dc.Caption == dc.ColumnName)
                    continue;
                sheet.AutoSizeColumn(colIndex, true);
                colIndex++;
            }
            sheet.PrintSetup.Landscape = Landscape;//设置横向打印
            using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            return saveFileName;
        }
        public static string GetDateTimeRnd(string name)
        {
            Random r = new Random(1000);
            return name + DateTime.Now.ToString("yyyyMMddHHmmssfff") + r.Next(10000, 99999).ToString();
        }

        //读取Excel 兼容2007以上版本
        public static DataTable ReadExcel(string strExcelFileName)
        {
            //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
            IWorkbook workbook;

            using (var file = new FileStream(strExcelFileName, FileMode.Open, FileAccess.Read))
            {
                if (strExcelFileName.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(file);
                }
                else // 2003版本
                {
                    workbook = new HSSFWorkbook(file);
                }
            }
            //获取excel的第一个sheet
            ISheet sheet = workbook.GetSheetAt(0);

            DataTable table = new DataTable();
            //获取sheet的首行
            IRow headerRow = sheet.GetRow(0);

            //一行最后一个方格的编号 即总的列数
            int cellCount = headerRow.PhysicalNumberOfCells;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            //最后一列的标号  即总的行数
            int rowCount = sheet.PhysicalNumberOfRows;

            bool hasValue = false;
            for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                    continue;
                DataRow dataRow = table.NewRow();
                hasValue = false;
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell != null)
                    {
                        dataRow[j] = cell.ToString();
                        if (!string.IsNullOrEmpty(cell.ToString()) && !hasValue)
                        {
                            hasValue = true;
                        }
                    }
                }
                if (hasValue)
                {
                    table.Rows.Add(dataRow);
                }
            }

            workbook = null;
            sheet = null;
            return table;
        }

        public static string ExportToExcel<T>(List<T> tList, string sheetName, string name, string saveFileName)
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            if (string.IsNullOrEmpty(saveFileName))
            {
                string filePath = GetDateTimeRnd(name) + ".xls";
                saveFileName = Path.Combine(path, filePath);
            }
            else
            {
                using (var file = new FileStream(saveFileName, FileMode.Open, FileAccess.Read))
                {
                    workbook = new HSSFWorkbook(file);
                }
            }


            HSSFSheet sheet = workbook.CreateSheet(sheetName) as HSSFSheet;

            Dictionary<PropertyInfo, string> dictCol = new Dictionary<PropertyInfo, string>();
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                object[] attrs = pi.GetCustomAttributes(typeof(DescriptionAttribute), false);
                if (attrs.Length > 0)
                {
                    dictCol.Add(pi, (attrs[0] as DescriptionAttribute).Description);
                }
            }

            //标题样式
            HSSFCellStyle cellTitleStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellTitleStyle.BorderBottom = BorderStyle.Thin;
            cellTitleStyle.BorderLeft = BorderStyle.Thin;
            cellTitleStyle.BorderRight = BorderStyle.Thin;
            cellTitleStyle.BorderTop = BorderStyle.Thin;

            HSSFFont dataTitleFont = workbook.CreateFont() as HSSFFont;
            dataTitleFont.FontName = "宋体";
            dataTitleFont.IsBold = true;
            dataTitleFont.FontHeightInPoints = 12;
            cellTitleStyle.SetFont(dataTitleFont);
            //标题
            HSSFRow row = sheet.CreateRow(0) as HSSFRow;
            int rowIndex = 1; int colIndex = 0;
            foreach (string desc in dictCol.Values)
            {
                HSSFCell cell = row.CreateCell(colIndex) as HSSFCell;
                cell.SetCellValue(desc);
                cell.CellStyle = cellTitleStyle;
                colIndex++;
            }

            //数据样式
            HSSFCellStyle cellStyle = workbook.CreateCellStyle() as HSSFCellStyle;
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            tList.ForEach(t =>
            {
                HSSFRow vRow = sheet.CreateRow(rowIndex) as HSSFRow;
                colIndex = 0;
                foreach (PropertyInfo pi in dictCol.Keys)
                {
                    HSSFCell cell = vRow.CreateCell(colIndex) as HSSFCell;
                    object val = pi.GetValue(t, null);
                    if (val != null)
                    {
                        if (pi.PropertyType == typeof(int))
                        {
                            int intV = 0;
                            int.TryParse(val.ToString(), out intV);
                            cell.SetCellType(CellType.Numeric);
                            cell.SetCellValue(intV);
                        }
                        else
                        {
                            cell.SetCellValue(val.ToString());
                        }
                    }
                    cell.CellStyle = cellStyle;
                    colIndex++;
                }
                rowIndex++;
            });

            //调整列宽
            colIndex = 0;
            foreach (string desc in dictCol.Values)
            {
                sheet.AutoSizeColumn(colIndex, true);
            }

            using (FileStream fs = new FileStream(saveFileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fs);
            }
            return saveFileName;
        }

        #region 根据模板导出excel
        public static string ExportByTemp<T>(T model, string tempPath, string saveFileName = "")
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            if (string.IsNullOrEmpty(saveFileName))
            {
                string filePath = GetDateTimeRnd("temp") + ".xls";
                saveFileName = Path.Combine(path, filePath);
            }

            using (FileStream fs = System.IO.File.OpenRead(tempPath))
            {
                HSSFWorkbook wk = new HSSFWorkbook(fs);
                fs.Close();
                ISheet sheet = wk.GetSheetAt(0);
                IRow firstRow = sheet.GetRow(0);
                ICellStyle styleTitle = wk.CreateCellStyle();
                styleTitle.WrapText = true;//自动换行
                //设置边框，设置字体为仿宋
                styleTitle.BorderLeft = BorderStyle.Thin;
                styleTitle.BorderRight = BorderStyle.Thin;
                styleTitle.BorderTop = BorderStyle.Thin;
                styleTitle.BorderBottom = BorderStyle.Thin;
                IFont font = wk.CreateFont();
                font.FontName = "仿宋";
                styleTitle.SetFont(font);
                //一行最后一个方格的编号 即总的列数
                int cellCount = firstRow.PhysicalNumberOfCells;
                //最后一行的标号  即总的行数
                int rowCount = sheet.PhysicalNumberOfRows;
                for (int i = sheet.FirstRowNum; i < rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null)
                        continue;
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            string cellval = cell.ToString();
                            if (cellval.StartsWith("{"))
                            {
                                cell.CellStyle = styleTitle;
                                string pName = cellval.TrimStart('{').TrimEnd('}');
                                if (pName == "R_")
                                {
                                    string desc = string.Empty;
                                    string val = GetSpecPi(typeof(T), model, "R_", out desc);
                                    cell.SetCellValue(desc);
                                    ICell valCell = row.GetCell(j + 1);
                                    valCell.SetCellValue(val);
                                }
                                else if (pName == "M_")
                                {
                                    string desc = string.Empty;
                                    string val = GetSpecPi(typeof(T), model, "M_", out desc);
                                    cell.SetCellValue(desc);
                                    ICell valCell = row.GetCell(j + 1);
                                    valCell.SetCellValue(val);
                                }
                                else
                                {
                                    PropertyInfo pi = typeof(T).GetProperty(pName);
                                    object v = pi.GetValue(model);
                                    if (v != null)
                                    {
                                        cell.SetCellValue(v.ToString());
                                    }
                                    else
                                    {
                                        cell.SetCellValue("");
                                    }
                                }
                            }
                        }
                    }

                }
                using (FileStream filess = File.OpenWrite(saveFileName))
                {
                    wk.Write(filess);
                }

            }
            return saveFileName;
        }

        public static string ExportListByTemp<T>(List<T> list, string tempPath, int beginIndex, string sheetName, string saveFileName = "")
        {
            string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Excel");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            if (string.IsNullOrEmpty(saveFileName))
            {
                string filePath = GetDateTimeRnd("temp") + ".xls";
                saveFileName = Path.Combine(path, filePath);
            }

            using (FileStream fs = System.IO.File.OpenRead(tempPath))
            {
                HSSFWorkbook wk = new HSSFWorkbook(fs);
                fs.Close();
                ISheet sheet = wk.GetSheet(sheetName);
                IRow firstRow = sheet.GetRow(0);
                ICellStyle styleTitle = wk.CreateCellStyle();
                styleTitle.WrapText = true;//自动换行
                //设置边框，设置字体为仿宋
                styleTitle.BorderLeft = BorderStyle.Thin;
                styleTitle.BorderRight = BorderStyle.Thin;
                styleTitle.BorderTop = BorderStyle.Thin;
                styleTitle.BorderBottom = BorderStyle.Thin;
                styleTitle.WrapText = true;
                IFont font = wk.CreateFont();
                font.FontName = "仿宋";
                styleTitle.SetFont(font);
                Dictionary<int, string> dictCol = new Dictionary<int, string>();

                IRow row = sheet.GetRow(beginIndex);
                //一行最后一个方格的编号 即总的列数
                int cellCount = row.PhysicalNumberOfCells;
                for (var i = 0; i < cellCount; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell != null && !string.IsNullOrEmpty(cell.StringCellValue))
                    {
                        dictCol.Add(i, cell.StringCellValue.Trim());
                        cell.SetCellValue("");
                    }
                }
                int index = beginIndex;
                foreach (var item in list)
                {
                    HSSFRow rowInfo = sheet.CreateRow(index) as HSSFRow;
                    foreach (var k in dictCol.Keys)
                    {
                        ICell cell = rowInfo.CreateCell(k);
                        if (dictCol[k].ToLower() == "no")
                        {
                            cell.SetCellValue((index - beginIndex + 1).ToString());
                        }
                        else
                        {
                            string val = GetVal<T>(item, dictCol[k]);
                            cell.SetCellValue(val);
                        }
                        cell.CellStyle = styleTitle;
                    }
                    index++;
                }

                //设置是否掩藏列
                //最后一行的标号  即总的行数
                int rowCount = sheet.PhysicalNumberOfRows;
                for (var i = 0; i < cellCount; i++)
                {
                    bool isHide = true;
                    for (var r = beginIndex; r < rowCount; r++)
                    {
                        HSSFRow r1 = sheet.GetRow(r) as HSSFRow;
                        if (r1 == null)
                            break;
                        ICell cell1 = r1.GetCell(i);
                        if (cell1 != null && !string.IsNullOrEmpty(cell1.StringCellValue))
                        {
                            isHide = false;
                            break;
                        }
                    }
                    if (isHide)
                        sheet.SetColumnHidden(i, true);
                }
                using (FileStream filess = File.OpenWrite(saveFileName))
                {
                    wk.Write(filess);
                }

            }
            return saveFileName;
        }

        private static string GetSpecPi(Type t, object o, string startName, out string desc)
        {
            PropertyInfo[] piList = t.GetProperties();
            List<PropertyInfo> pInfos = piList.Where(p => p.Name.StartsWith(startName)).ToList();
            string val = string.Empty;
            desc = "";
            foreach (var p in pInfos)
            {
                object v = p.GetValue(o);
                if (v != null && v.ToString() != "" && v.ToString() != "-1")
                {
                    //获取其他说明
                    val = v.ToString();
                    string specPiName = p.Name + "_Info";
                    PropertyInfo specPi = piList.Where(pi => pi.Name == specPiName).FirstOrDefault();
                    if (specPi != null)
                    {
                        object specV = specPi.GetValue(o);
                        if (specV != null && specV.ToString() != "")
                        {
                            val += ";" + specV.ToString();
                        }
                    }

                    DescriptionAttribute d = p.GetCustomAttribute(typeof(DescriptionAttribute)) as DescriptionAttribute;
                    if (d != null && !string.IsNullOrEmpty(d.Description))
                    {
                        desc = d.Description;
                    }
                }
            }
            return val;
        }

        private static string GetVal<T>(T model, string pyName)
        {
            Type t = model.GetType();
            PropertyInfo p = t.GetProperty(pyName);
            if (p == null)
                return string.Empty;
            object o = p.GetValue(model);
            return o == null ? string.Empty : o.ToString();
        }

        public static void DelSheetbyRowIndex(string tempPath, string sheetName, int beginIndex)
        {
            using (FileStream fs = System.IO.File.OpenRead(tempPath))
            {
                HSSFWorkbook wk = new HSSFWorkbook(fs);
                fs.Close();
                ISheet sheet = wk.GetSheet(sheetName);
                IRow row = sheet.GetRow(beginIndex);
                ICell cell = row.GetCell(0);
                logger.Error("sheet:" + sheet.SheetName + ",单元格数据：" + cell.StringCellValue);
                if (string.IsNullOrEmpty(cell.StringCellValue) || cell.StringCellValue.ToLower() == "no")
                {
                    wk.SetSheetHidden(wk.GetSheetIndex(sheetName), true);
                    logger.Error("被删除的sheet:" + sheetName);
                }

                using (FileStream filess = File.OpenWrite(tempPath))
                {
                    wk.Write(filess);
                }
            }

        }
        #endregion

    }
}
